package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.ClassRoom;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 教室 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-07-26
 */
public interface ClassRoomMapper extends BaseMapper<ClassRoom> {

    @Select("select id,name,school_id,getSchoolName(school_id) as schoolName from class_room ${ew.customSqlSegment}")
    public abstract IPage<Map<String,Object>> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select({"<script>",
            "select cr.id,cr.name,cr.school_id,getSchoolName(cr.school_id) as schoolName,",
            "CASE WHEN g.class_room_id IS NOT NULL THEN 1 ELSE 0 END AS schedulingStatus ",
            "from class_room AS cr",
            "LEFT JOIN grade AS g ON cr.id = g.class_room_id ",
            "<when test='yearPart!=null and yearPart!=&apos;&apos; '>",
                "AND g.year_part = ${yearPart}",
            "</when>",
            "<when test='quarterNum!=null and quarterNum!=&apos;&apos; '>",
                "AND g.quarter_num = ${quarterNum} ",
            "</when>",
            "<when test='timeStart!=null and timeStart!=&apos;&apos; '>",
                "AND g.time_start = '${timeStart}' ",
            "</when>",
            "<when test='timeEnd!=null and timeEnd!=&apos;&apos; '>",
                "AND g.time_end = '${timeEnd}' ",
            "</when>",
            "<when test='weekDates!=null and weekDates!=&apos;&apos; '>",
                "AND g.week_dates = '${weekDates}' ",
            "</when>",
            "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
                "AND g.lesson_type = ${lessonType} ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "AND g.school_id IN (${schoolId}) ",
            "</when>",
            "AND g.deleted = 0",
            "${ew.customSqlSegment} ",
            "</script>"
    })
    public abstract IPage<Map<String, Object>> checkClassRoomByStatus(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("schoolId") String schoolId, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("lessonType") String lessonType, @Param("timeStart") String timeStart, @Param("timeEnd") String timeEnd, @Param("weekDates") String weekDates);

    @Select({"<script>",
                "select class_room_id as id,b.`name` from grade as a LEFT JOIN class_room as b on a.class_room_id = b.id ",
                    "where",
                    "a.year_part = ${yearPart} and a.quarter_num = ${quarterNum} and a.lesson_type = ${lessonType} and a.school_id = ${schoolId} AND b.deleted = 0 and a.class_room_id IS NOT NULL",
                    "<when test='sourseLeve == 2'>",
                        "and a.teacher_id = ${adminId}",
                    "</when>",
                    "GROUP BY a.class_room_id",
            "</script>"
    })
    public abstract List<ClassRoom> getGradeClassRoomList(@Param("schoolId") Long schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("lessonType") Integer lessonType,@Param("sourseLeve") Integer sourseLeve,@Param("adminId") Long adminId);


    @Select("select b.id,a.`name` as gradeName,week_dates as weekDates,b.`name` from grade as a LEFT JOIN class_room as b on a.class_room_id = b.id where a.year_part = ${yearPart} and a.quarter_num = ${quarterNum} and a.lesson_type = ${lessonType} and a.school_id = ${schoolId} AND b.deleted = 0 AND a.deleted = 0 and a.class_room_id IS NOT NULL AND a.week_dates >${begin} AND a.week_dates <${end} ORDER BY a.time_start ASC ")
    public abstract List<ClassRoom> getGradeClassRooms(@Param("schoolId") Long schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("begin") Integer begin,@Param("end") Integer end,@Param("lessonType") Integer lessonType);

    @Select("select b.id,a.`name` as gradeName,week_dates as weekDates,b.`name`,CONCAT( a.time_start, '-', a.time_end ) AS school_time  from grade as a LEFT JOIN class_room as b on a.class_room_id = b.id where a.year_part = ${yearPart} and a.quarter_num = ${quarterNum} and a.lesson_type = ${lessonType} and a.school_id = ${schoolId} AND b.deleted = 0 AND a.deleted = 0 and a.class_room_id IS NOT NULL AND a.week_dates = '${week}' ORDER BY a.time_start ASC ")
    public abstract List<ClassRoom> getGradeClassRooms1(@Param("schoolId") Long schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("week") String week,@Param("lessonType") Integer lessonType);

    // 周末
    @Select("select b.id,a.`name` as gradeName,week_dates as weekDates,b.`name`,CONCAT( a.time_start, '-', a.time_end ) AS school_time,c.subjects_id as subjectsId  from grade as a LEFT JOIN class_room as b on a.class_room_id = b.id LEFT JOIN course AS c ON a.course_id = c.id where a.year_part = ${yearPart} and a.quarter_num = ${quarterNum} and a.lesson_type = ${lessonType} and a.school_id = ${schoolId} AND b.deleted = 0 AND a.deleted = 0 and a.class_room_id IS NOT NULL AND a.week_dates = '${week}' ORDER BY CASE WHEN b.`name` REGEXP '^[0-9]+' THEN CAST( b.`name` AS UNSIGNED ) ELSE 999999999999999 END,b.`name` ASC ")
    public abstract List<ClassRoom> getGradeClassRoomsByName(@Param("schoolId") Long schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("week") String week,@Param("lessonType") Integer lessonType);

    //周内
    @Select("select b.id,a.`name` as gradeName,week_dates as weekDates,b.`name`,c.subjects_id as subjectsId  from grade as a LEFT JOIN class_room as b on a.class_room_id = b.id LEFT JOIN course AS c ON a.course_id = c.id  where a.year_part = ${yearPart} and a.quarter_num = ${quarterNum} and a.lesson_type = ${lessonType} and a.school_id = ${schoolId} AND b.deleted = 0 AND a.deleted = 0 and a.class_room_id IS NOT NULL AND a.week_dates >${begin} AND a.week_dates <${end} ORDER BY CASE WHEN b.`name` REGEXP '^[0-9]+' THEN CAST( b.`name` AS UNSIGNED ) ELSE 999999999999999 END,b.`name` ASC ")
    public abstract List<ClassRoom> getGradeClassRoomsByName1(@Param("schoolId") Long schoolId,@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("begin") Integer begin,@Param("end") Integer end,@Param("lessonType") Integer lessonType);

}
